package com.demo.util;  
  
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.Column;
import javax.persistence.Table;

import org.apache.commons.beanutils.BeanUtils;

import com.demo.bean.Entity2SqlMap;
import com.demo.cache.CacheSupport;
import com.shopcart.domain.Store;
  
  
public class SqlUtils {  
    /** 
     * @param args 
     * @throws Exception 
     * @throws IllegalArgumentException 
     */  
    public static void main(String[] args) throws IllegalArgumentException, Exception {  
    	Store s = new Store();
    	s.setAccount("123");
    	s.setAddress("456");
         System.out.println(SqlUtils.update(s, true, " where id='1'"));
    }  
    /**
     * 保存一个对象的sql语句。最后两个参数是临时为止，
     * 他们的存在令人很不爽。
     * @param bean
     * @param sql 用于带回sql语句的参数
     * @param params 用于带回参数的参数
     * @return 
     */
    public static String save(Object bean){
        Class<? extends Object> beanClass = bean.getClass();
        List<String> fields   = new ArrayList<String>();
        List<Method> getters  = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        /**开始拼装sql和params*/
        List<Object> tempParams = new ArrayList<Object>();
        String insertSql = "insert into " + beanClass.getSimpleName()+" ";
        String valueSql  = "";
        String columnSql = "";
        Method getter    = null;
        String field     = null;
        for(int i=0;i<fields.size();i++){
            getter = getters.get(i);
            field  =  fields.get(i);
            columnSql += (field+",");
            valueSql += "?,";
            try{
                tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        columnSql = ( "(" + columnSql + ")").replace(",)", ")");
        valueSql = ( "(" + valueSql + ")").replace(",)", ")");
        return insertSql+columnSql+" values "+valueSql;
    }
      
    /**
     * 更新一条记录
     * @param bean 所要更新的表
     * @param columns 所要查询的字段
     * @param whereSql 查询的条件
     * @return 
     */
    public static String update(Object bean,String[] columns,String whereSql){
        Class<? extends Object> beanClass = bean.getClass();
        List<String> fields  = new ArrayList<String>();
        List<Method> getters = new ArrayList<Method>();
        getGetters(beanClass, fields, getters);
         
        /**过滤出要更新的字段*/
        int oldSize = fields.size();
        int index = -1;
        for(int i=0;i<columns.length;i++){
            index = fields.indexOf(columns[i].toString().toLowerCase());
            if(index != -1){
                fields.add(fields.get(index));
                getters.add(getters.get(index));
            }
        }
        int newSize = fields.size();
        fields = fields.subList(oldSize,newSize);
        getters = getters.subList(oldSize, newSize);
         
        /**开始拼装sql和params*/
        List<Object> tempParams = new ArrayList<Object>();
        String updateSql = "update " + beanClass.getSimpleName() + " set ";
        String columnSql = "";
        Method getter    = null;
        String field     = null;
        for(int i=0;i<fields.size();i++){
            getter = getters.get(i);
            field  =  fields.get(i);
            columnSql += ("," + field+"=?");
            try{
                tempParams.add(getter.invoke(bean, null));
            }catch (Exception e) {
                e.printStackTrace();
            }
        }
        columnSql = columnSql.replaceFirst(",", "");
        /**返回*/
        return columnSql;
    }
    
    /**
     * @param bean 要持久化得对象
     * @param breakNull 持久化时是否忽略值为null的属性
     * @param whereSql 查询条件
     * @return 
     * @throws Exception 
     * @throws IllegalArgumentException 
     */
    public static String update(Object bean,boolean breakNull,String whereSql) throws IllegalArgumentException, Exception{
        Class<? extends Object> beanClass = bean.getClass();
        Entity2SqlMap map = getValues(beanClass);
        List<String> fields = map.getFields();
        
        Map<String, Object> resMap = new HashMap<>();
        Map<String, Method> getters = map.getGetters();
        Method getter = null;
        for(int i=0;i<fields.size();i++){
            getter = getters.get(fields.get(i));
            try {
            	Object o = getter.invoke(bean, null);
            	if(!breakNull){
            		if(o!=null&&o instanceof Date){
            			Date d = (Date) o;
            			try {
							String s = DateUtil.dateToStr(d, "yyyy-MM-dd HH:mm:ss");
							resMap.put(fields.get(i), s);
						} catch (Exception e) {
							String s  = DateUtil.dateToStr(d, "yyyy-MM-dd");
							resMap.put(fields.get(i), s);
						}
            		}else{
            			resMap.put(fields.get(i), o);
            		}
            		
            	}else{
            		/**排除null属性，不予保存*/
            		if(o!=null){
            			if(o!=null&&o instanceof Date){
                			Date d = (Date) o;
                			try {
    							String s = DateUtil.dateToStr(d, "yyyy-MM-dd HH:mm:ss");
    							resMap.put(fields.get(i), s);
    						} catch (Exception e) {
    							String s  = DateUtil.dateToStr(d, "yyyy-MM-dd");
    							resMap.put(fields.get(i), s);
    						}
                		}else{
                			resMap.put(fields.get(i), o);
                		}
            		}
            	}
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        
        /**开始拼接sql语句*/
        String updateSql = "update " + beanClass.getAnnotation(Table.class).name() + " set ";
        String columnSql = "";
        for (String key : resMap.keySet()) {
        	 columnSql += ("," + key+"= '"+resMap.get(key)+"'");
		}
        columnSql = columnSql.replaceFirst(",", "");
        return updateSql+columnSql+whereSql;
    }
    
    
    /**
     * 拼接出一个select sql 语句
     * @param table 所要查询的表
     * @param columns 所要查询的字段
     * @param whereSql 查询的条件
     * @return 
     */
    public static String select(String table,String[] columns,String whereSql){
        /**开始拼装sql和params*/
        String selectSql = "select ? from " + table + " ";
        String columnSql = "";
        for(int i=0;i<columns.length;i++){
            columnSql += (","+columns[i]);
        }
        columnSql = columnSql.replaceFirst(",", "");
        selectSql = selectSql.replace("?", columnSql);
        return selectSql+" "+whereSql;
    }
    /**
     * 找到指定类的所有getters()方法，但是去除Object父类的getClass()方法。
     * @param beanClass 指定的持久化对象
     * @param fields 返回所有写有getters的方法的属性，Object父类的class除外   
     * @param getters 返回所有除了从Object继承的getters方法
     */
    private static void getGetters(Class<? extends Object> beanClass,List<String> fields,List<Method> getters){
        Method[] methods = beanClass.getMethods();
         
        HashMap<String,Method> map = new HashMap<String,Method>();
        String field = null,methodName = null;
        for(Method meth:methods){
            methodName  = meth.getName();
            field       = methodName.substring(3).toLowerCase();
             
            if(methodName.substring(0, 3).equals("get")){
                map.put(field,meth);
            }
        }
        map.remove("class");
        map.entrySet();
        for(Entry<String,Method> item:map.entrySet()){
            fields.add(item.getKey());
            getters.add(item.getValue());
        }
    }
    
    /**
     * 找到指定类的所有getters()方法，但是去除Object父类的getClass()方法。
     * @param beanClass 指定的持久化对象
     * @throws Exception 
     * @throws IllegalArgumentException 
     * @return 返回sql 列名以及getter方法
     */
    private static Entity2SqlMap getValues(Class<? extends Object> beanClass) throws IllegalArgumentException, Exception{
    	
    	Entity2SqlMap map = CacheSupport.get("entityCache", beanClass.getName(), Entity2SqlMap.class);
    	if(map==null){
	    		map = new Entity2SqlMap();
	    		Field[] methods = beanClass.getDeclaredFields();
	    		Class sup = beanClass.getSuperclass();
	    		Field[] methods1={};
	    		if(sup!=null) methods1= beanClass.getSuperclass().getDeclaredFields();
	    		
	    		Map<String, Method> fieldMap = new HashMap<>();
	    		List<String> fields=new LinkedList<>();
	    		Map<String,String> columns=new HashMap<>();
    	        String field = null;
    	        //父类先
    	        for(Field f:methods1){
    	        	field  = f.getName();
    	        	Method m = beanClass.getMethod("get"+field.substring(0,1).toUpperCase()+field.substring(1));
    	            Column c = f.getAnnotation(Column.class);
    	            fields.add(c==null?field:c.name());
    	            
    	            fieldMap.put(c==null?field:c.name(), m);
    	            columns.put(c==null?field:c.name(), field);
    	        }
    	        
    	        for(Field f:methods){
    	        	field  = f.getName();
    	        	Method m = beanClass.getMethod("get"+field.substring(0,1).toUpperCase()+field.substring(1));
    	            Column c = f.getAnnotation(Column.class);
    	            fields.add(c==null?field:c.name());
    	            
    	            fieldMap.put(c==null?field:c.name(), m);
    	            columns.put(c==null?field:c.name(), field);
    	        }
    	        
    	        map.setFields(fields);
    	        map.setGetters(fieldMap);
    	        map.setColumns(columns);
    	        CacheSupport.put("entityCache", beanClass.getName(), map);
    	}
    	return map;
       
    }
    
    // Map --> Bean 2: 利用org.apache.commons.beanutils 工具类实现 Map --> Bean  
    public static void transMap2Bean2(Map<String, Object> map, Object obj) {
    	if (map == null || obj == null) {
            return;  
        } 
    	Map<String, Object> tempMap = map; 
    	try {
			Entity2SqlMap sqlMap = getValues(obj.getClass());
			Map<String, String> columns = sqlMap.getColumns();
			tempMap = new HashMap<>();
			for(String field:columns.keySet()){
				String column = columns.get(field);
				if(map.get(field)!=null){
					tempMap.put(column, map.get(field));
				}
			}
		}  catch (Exception e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
    	
        try {  
            BeanUtils.populate(obj, tempMap);
        } catch (Exception e) {  
            System.out.println("transMap2Bean2 Error " + e);  
        }  
    }
    
    
}  